<?php

$get_all_nub_sql = <<<SQL
CREATE TABLE taxon_id_from_occurrence AS
  SELECT DISTINCT
    o.nub_concept_id AS id,
    tc.rank AS rank_id
  FROM occurrence_record o
  LEFT JOIN taxon_concept tc ON tc.id = o.nub_concept_id
  WHERE o.iso_country_code = :iso_a2
  AND o.geospatial_issue = 0 
  AND tc.rank >= 7000
  AND tc.is_nub_concept = 1
  AND tc.is_accepted = 1;
SQL;

$get_all_nub_sql_index = <<<SQL
ALTER TABLE taxon_id_from_occurrence add index(id);
SQL;

/* Make sure species id of sub specific entries are there */
$get_all_nub_sql_sp = <<<SQL
INSERT INTO taxon_id_from_occurrence
  SELECT DISTINCT
    sc.id AS id,
    sc.rank AS rank_id
  FROM taxon_id_from_occurrence t
  LEFT JOIN taxon_concept tc on tc.id = t.id
  LEFT JOIN taxon_concept sc on sc.id = tc.species_concept_id
  WHERE t.rank_id >= 8000;
SQL;

/* Retrieve only unique entries */
$get_all_nub_sql_distinct = <<<SQL
CREATE TABLE taxon_id_from_occurrence_distinct AS
  SELECT DISTINCT id FROM taxon_id_from_occurrence;
SQL;

$get_all_nub_sql_dis_index = <<<SQL
ALTER TABLE taxon_id_from_occurrence_distinct add index(id);
SQL;

/* Drop the table no longer needed */
$get_all_nub_sql_drop = <<<SQL
DROP TABLE taxon_id_from_occurrence;
SQL;

/* Insert records for sub species level names */
$collect_names_sql = <<<SQL
CREATE TABLE parent_child_bko AS
SELECT DISTINCT
t.id,
tn.canonical AS scientificName,
sc.id AS parentID,
sn.canonical AS parentNameUsage,
tn.author AS author,
r.name AS rank,
tn.infraspecific_marker AS unitIndicator3,
tn.generic AS unitName1,
tn.specific_epithet AS unitName2,
tn.infraspecific AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_name tn ON tc.taxon_name_id = tn.id
LEFT JOIN taxon_concept sc ON sc.id = tc.species_concept_id
LEFT JOIN taxon_name sn ON sc.taxon_name_id = sn.id
LEFT JOIN rank r ON tc.rank = r.id
WHERE
tc.rank >= 8000
ORDER BY rank;
SQL;

/* Insert records for species level names */
$collect_names_sql_s = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
t.id,
tn.canonical AS scientificName,
gc.id AS parentID,
gn.canonical AS parentNameUsage,
tn.author AS author,
r.name AS rank,
tn.infraspecific_marker AS unitIndicator3,
tn.generic AS unitName1,
tn.specific_epithet AS unitName2,
tn.infraspecific AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_name tn ON tc.taxon_name_id = tn.id
LEFT JOIN taxon_concept gc ON gc.id = tc.genus_concept_id
LEFT JOIN taxon_name gn ON gc.taxon_name_id = gn.id
LEFT JOIN rank r ON tc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert genus level names based on species */
$collect_names_sql_g = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
gc.id,
gn.canonical AS scientificName,
fc.id AS parentID,
fn.canonical AS parentNameUsage,
gn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
gn.generic AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept gc ON gc.id = tc.genus_concept_id
LEFT JOIN taxon_name gn ON gc.taxon_name_id = gn.id
LEFT JOIN taxon_concept fc ON fc.id = tc.family_concept_id
LEFT JOIN taxon_name fn ON fc.taxon_name_id = fn.id
LEFT JOIN rank r ON gc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert family level names based on species */
$collect_names_sql_f = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
fc.id,
fn.canonical AS scientificName,
odc.id AS parentID,
odn.canonical AS parentNameUsage,
fn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
fn.canonical AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept fc ON fc.id = tc.family_concept_id
LEFT JOIN taxon_name fn ON fc.taxon_name_id = fn.id
LEFT JOIN taxon_concept odc ON odc.id = tc.order_concept_id
LEFT JOIN taxon_name odn ON odc.taxon_name_id = odn.id
LEFT JOIN rank r ON fc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert order level names based on species */
$collect_names_sql_o = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
odc.id,
odn.canonical AS scientificName,
cc.id AS parentID,
cn.canonical AS parentNameUsage,
odn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
odn.canonical AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept odc ON odc.id = tc.order_concept_id
LEFT JOIN taxon_name odn ON odc.taxon_name_id = odn.id
LEFT JOIN taxon_concept cc ON cc.id = tc.class_concept_id
LEFT JOIN taxon_name cn ON cc.taxon_name_id = cn.id
LEFT JOIN rank r ON odc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert class level names based on species */
$collect_names_sql_c = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
cc.id,
cn.canonical AS scientificName,
pc.id AS parentID,
pn.canonical AS parentNameUsage,
cn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
cn.canonical AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept cc ON cc.id = tc.class_concept_id
LEFT JOIN taxon_name cn ON cc.taxon_name_id = cn.id
LEFT JOIN taxon_concept pc ON pc.id = tc.phylum_concept_id
LEFT JOIN taxon_name pn ON pc.taxon_name_id = pn.id
LEFT JOIN rank r ON cc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert phylum level names based on species */
$collect_names_sql_p = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
pc.id,
pn.canonical AS scientificName,
kc.id AS parentID,
kn.canonical AS parentNameUsage,
pn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
pn.canonical AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept pc ON pc.id = tc.phylum_concept_id
LEFT JOIN taxon_name pn ON pc.taxon_name_id = pn.id
LEFT JOIN taxon_concept kc ON kc.id = tc.kingdom_concept_id
LEFT JOIN taxon_name kn ON kc.taxon_name_id = kn.id
LEFT JOIN rank r ON pc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Insert kingdom level names based on species */
$collect_names_sql_k = <<<SQL
INSERT INTO parent_child_bko
SELECT DISTINCT
kc.id,
kn.canonical AS scientificName,
doc.id AS parentID,
don.canonical AS parentNameUsage,
kn.author AS author,
r.name AS rank,
NULL AS unitIndicator3,
kn.canonical AS unitName1,
NULL AS unitName2,
NULL AS unitName3,
r.id AS rank_id
FROM taxon_id_from_occurrence_distinct t
LEFT JOIN taxon_concept tc ON tc.id = t.id
LEFT JOIN taxon_concept kc ON kc.id = tc.kingdom_concept_id
LEFT JOIN taxon_name kn ON kc.taxon_name_id = kn.id
LEFT JOIN taxon_concept doc ON doc.id = kc.parent_concept_id
LEFT JOIN taxon_name don ON doc.taxon_name_id = don.id
LEFT JOIN rank r ON kc.rank = r.id
WHERE
tc.rank = 7000
ORDER BY rank;
SQL;

/* Delete records that we'll have no clue to process. */
$collect_names_sql_delete = <<<SQL
DELETE FROM parent_child_bko WHERE id IS NULL;
SQL;

/* Capitalise the first letter of rank */
$capitalise_rank_sql = <<<SQL
UPDATE parent_child_bko SET rank = CONCAT(UPPER(LEFT(rank, 1)), SUBSTRING(rank, 2));
SQL;

$export_csv_sql = <<<SQL
SELECT pc.* INTO OUTFILE '/tmp/checklist_occu.csv' FIELDS TERMINATED BY '\t' ESCAPED BY '' LINES TERMINATED BY '\n' FROM parent_child_bko pc ORDER BY pc.rank_id, pc.parentID, pc.id;
SQL;

$export_csv_cmd = <<<CMD
/usr/local/zend/mysql/bin/mysql.client name_processing -e "SELECT pc.scientificName, if(pc.parentNameUsage is not null, pc.parentNameUsage, ''), '', pc.id, if(pc.parentID is not null, pc.parentID, ''), '', if(pc.author is not null, pc.author, ''), '', pc.rank, '', '', '', '', if(pc.unitIndicator3 is not null, pc.unitIndicator3, ''), '', if(pc.unitName1 is not null, pc.unitName1, ''), if(pc.unitName2 is not null, pc.unitName2, ''), if(pc.unitName3 is not null, pc.unitName3, ''), '', 'valid' INTO OUTFILE :file FIELDS TERMINATED BY '\t' ESCAPED BY '' LINES TERMINATED BY '\n' FROM parent_child_bko pc ORDER BY pc.rank_id, pc.parentID, pc.id;"
CMD;

$chmod_cmd = <<<'CMD'
echo :pass | sudo -u root -S chmod 777 :file
CMD;

$delete_cmd = <<<'CMD'
echo :pass | sudo -u root -S rm -rf :file
CMD;

$clean_db_sql_t = <<<SQL
DROP TABLE taxon_id_from_occurrence_distinct;
SQL;

$clean_db_sql_pc = <<<SQL
DROP TABLE parent_child_bko;
SQL;


?>